/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package persona;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author aralvarezm
 */
public class conexionpersona {

    private static final String SQL_INSERT
            = "INSERT INTO persona "
            + "VALUES (?,?,?,?,?,?,?,?,?)"; //esos son pra los campos son 9 
    private static final String SQL_SELECT_AlUMNO
            = "SELECT * "
            + "FROM persona WHERE cedula = ?";
    private static final String SQL_SELECT_All
            = "SELECT * "
            + "FROM persona";

    private static final String SQL_UPDATE
            = "UPDATE alumno SET "
            + "n_cedula = ?, nombre = ?, apellido = ?, f_nacimiento = ?, sexo = ?,"
            + "fe_bautismo = ?, n_telefono = ?, costo = ?, estado = ?"
            + " WHERE "
            + "id_alumno = ? ";

    /* SQL to delete data */
    private static final String SQL_DELETE
            = "DELETE FROM alumno WHERE "
            + "id_alumno = ?";

    public void ingresarpersona(Persona p, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {

            ps = (PreparedStatement) conn.prepareStatement(SQL_INSERT);
            System.out.println("Tabla alumno abierta");

            ps.setString(1, p.getNombres());
            ps.setString(2, p.getApellidos());
            ps.setString(3, p.getCedula());
            ps.setString(4, p.getDireccion());
            ps.setString(5, p.getMail());
            ps.setString(6, p.getSexo());
            ps.setInt(7, p.getEdad());
            ps.setInt(8, p.getTelefono());
            //ps.setInt(7,p.getId());

            if (ps.executeUpdate() != 1) {
                System.out.println("Error, datos no insertados.");
            } else {
                System.out.println("Datos insertados correctamente.");
            }

        } catch (Exception e) {
            System.out.print("Error al abrir la tabla alumno " + e);

        } finally {
            cerrar(ps);
            cerrar(conn);
        }

    }

    public void actualizarPersona(Persona p, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        try {

            ps = (PreparedStatement) conn.prepareStatement(SQL_UPDATE);
            System.out.println("Tabla alumno abierta");

            ps.setString(1, p.getNombres());
            ps.setString(2, p.getApellidos());
            ps.setString(3, p.getCedula());
            ps.setString(4, p.getDireccion());
            ps.setString(5, p.getMail());
            ps.setString(6, p.getSexo());
            ps.setInt(7, p.getEdad());
            ps.setInt(8, p.getTelefono());
            //ps.setInt(7,p.getId());

            if (ps.executeUpdate() != 1) {
                System.out.println("Error, datos no actualizados.");
            } else {
                System.out.println("Datos actualizados correctamente.");
            }

        } catch (Exception e) {
            System.out.print("Error al abrir la tabla alumno " + e);
        } finally {
            cerrar(ps);
            cerrar(conn);
        }
    }

    public Persona consultarAlumno(String alumno_id, Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = (PreparedStatement) conn.prepareStatement(SQL_SELECT_AlUMNO);
            System.out.println("Tabla alumno abierta");

            ps.setString(1, alumno_id);
            rs = (ResultSet) ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return (Persona) results.get(0);
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
        }
    }

    public List consultarTodos(Connection conn) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = (PreparedStatement) conn.prepareStatement(SQL_SELECT_All);
            System.out.println("Tabla alumno abierta");

            rs = (ResultSet) ps.executeQuery();
            List results = getResults(rs);
            if (results.size() > 0) {
                return results;
            } else {
                return null;
            }

        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
        }
    }

    private List getResults(ResultSet rs) throws SQLException {
        List results = new ArrayList();
        Persona p;
        while (rs.next()) {
            p = new Persona();
//            al.setId_alumno(rs.getString("id_alumno"));
//            al.setId_grupo(rs.getString("id_grupo"));
//            al.setN_cedula(rs.getInt("n_cedula"));
//            al.setNombre(rs.getString("nombre"));
//            al.setApellido(rs.getString("apellido"));
//            al.setF_nacimiento(rs.getDate("f_nacimiento"));
//            al.setSexo((rs.getString("sexo").charAt(0)));
//            al.setFe_bautismo(rs.getString("fe_bautismo"));
//            al.setN_telefono(rs.getInt("n_telefono"));
//            al.setCosto(rs.getFloat("costo"));
//            al.setEstado(rs.getString("estado"));

            //System.out.println(rs.getString(1) + rs.getInt(2));
            results.add(p);
        }
        return results;
    }

    private void cerrar(PreparedStatement ps) throws SQLException {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrar(Statement st) throws SQLException {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrar(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
    }

    private void cerrar(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException e) {
            }
        }
    }

}
